package com.lsu.app.mapper;

import com.lsu.app.bean.Vocabulary;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 *
 * @description 针对表【vocabulary(分词器频率词)】的数据库操作Mapper
 * @Date 2023-11-13 19:48:25
 * @author hjj
 */
@Mapper
public interface VocabularyMapper extends BaseMapper<Vocabulary> {
    @Select("select id from vocabulary where words=#{word} and course_id=#{courseId}")
    Integer getIdByWord(@Param("word") String word,@Param("courseId") Integer courseId);
    @Update("update vocabulary set frequency=frequency+1 where id=#{id}")
    void addFrById(Integer id);
    @Select("select words from vocabulary where course_id=#{courseId} order by frequency limit 3")
    List<String> getQuWordsByCourseId(Integer courseId);//获取词频最高的三个词
    @Select("select SUM(frequency) from vocabulary where course_id=#{courseId} and type=#{type}")
    Integer getCountByCourseIdAndType(@Param("courseId") Integer courseId, @Param("type") Integer type);//根据课程id和类型 获取条数
    @Insert("insert into user_vocabulary values (null,#{vocaId},#{userId})")
    void insertAllWithUV(@Param("userId") Integer userId,@Param("vocaId") Integer vocaId);//插入用户-词频键值对
    @Select("select user_id from user_vocabulary where vocabulary_id in (select id from vocabulary where words=#{word} and course_id=#{courseId})")
    List<Integer> getUserIdByWord(@Param("word") String word,@Param("courseId") Integer courseId);//获取用户id
    @Select("select course_Id from vocabulary where words=#{word}")
    List<Integer> getCourseIdByWord(String word);//获取课程id

    @Select("select words,SUM(frequency) as frequency from vocabulary where course_id in(select id from course where teacher_id =#{teacherId}) group by words")
    List<Vocabulary> getVocasByTeacherId(Integer teacherId);
}




